<?php
class Customers extends Model{
	
	function getAll($id=0){
		$sql = " SELECT *,c.id as id,point as total "; 
		//$sql.= " (SELECT SUM(id) FROM activity_transaction at WHERE at.customer_id=c.id) as total";
		$sql.= " FROM customers c ";
		$sql.= " LEFT JOIN customer_status s ON s.id=c.status_id ";
		//$sql.="LEFT JOIN user_position p ON p.id=u.pos_id ";
        //$sql.="WHERE rev_group=0 ";
		$sql.="WHERE customer_name<>'' ";
		if($this->session->userdata('user_group')<> $this->config->item('admin')):
			//$sql.="AND c.spv_approve = 1 ";
		endif;
        //if($this->session->userdata('user_group')!= $this->config->item('admin') ): 
        //if(($this->session->userdata('user_pos')== $this->config->item('ta') ) ):
         //   if(($this->session->userdata('user_group')== $this->config->item('user') )   ):
		if(($this->session->userdata('user_pos')== $this->config->item('ta'))): 	
                $sql.="AND  rev ='".$this->session->userdata('user_id')."'";
		elseif(($this->session->userdata('user_pos')== $this->config->item('se'))):
				 $sql.="AND rev2='".$this->session->userdata('user_id')."' AND latest_activity <> 4 ";
				 if($id!='trash'){
					$sql.=" AND latest_activity<>2 ";
				 } else {
					$sql.=" AND latest_activity = 2 ";
				 }
		elseif(($this->session->userdata('user_pos')== $this->config->item('ae'))):
				 $sql.="AND rev3='".$this->session->userdata('user_id')."'";
	    endif;			 
				 
         //   endif;
        //elseif(($this->session->userdata('user_pos')== $this->config->item('se') ) ):
        //    if(($this->session->userdata('user_group')== $this->config->item('user') )   ): 
        //        $sql.="AND rev='".$this->session->userdata('user_ta')."'";
         //   endif;    
        //endif;
        //endif;
		$sql.="ORDER BY point ASC "; 
        //(SELECT COUNT(*) FROM activity_transaction at WHERE at.customer_id=c.id)
		//$sql.=" ORDER BY (SELECT COUNT(*) FROM activity_transaction at WHERE at.customer_id=c.id LIMIT 1) ASC LIMIT 5"; 
		//$sql.=" LIMIT 5";
		$query=$this->db->query($sql);
		//$query->free_result();
		return $query->result_array();
	}
    
    function getUserJob(){
        $sql =" SELECT u.username ";
        //$sql.="(SELECT SUM( ((p.invest + (p.invest * j.ppn / 100) - j.discount)) ) FROM jobs j JOIN customers c ON c.id=j.customer_id JOIN products p ON p.id=j.product_id WHERE c.rev=u.username) as total ";
        $sql.=" FROM users u ";
		/*$sql.=" WHERE u.username='MKI' ";
		$sql.=" OR u.username='NSI' ";
		$sql.=" OR u.username='PRO' ";
		$sql.=" OR u.username='RBA' ";*/
        $sql.=" WHERE pos_id=".$this->config->item('ta');
        $sql.=" AND sales=1 ";
        $sql.=" AND group_id=".$this->config->item('user');
        $query=$this->db->query($sql);
		return $query->result_array();
    }
	
	function getActiveUsers($pos=''){
		$sql  = "SELECT * ";
		$sql .= "FROM ";
		$sql .= "users ";
		$sql .= "WHERE pos_id=".$pos;
		$Q    = $this->db->query($sql);
		$data = $Q->result_array();
		$data = array();
		if ($Q->num_rows() > 0):
			foreach ($Q->result_array() as $row)
			{
				$data[0]				  = '';	
				$data[$row['username']] = $row['username'];
			}
		endif;
		$Q->free_result();
		return $data;
	}
    
    function getProspect(){
		$sql ="SELECT * FROM prospects p ";
		$sql.=" WHERE p.rev='' ";
        $sql.=" ORDER BY p.id ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getProspectUsers($id){
		$sql ="SELECT * FROM prospects p ";
		$sql.=" WHERE p.rev<>'' ";
		if($id<>""):
		$sql.=" AND p.rev='".$id."'";	
		endif;
		$sql.=" ORDER BY p.id ASC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getLastActivity($id){
        $sql =" SELECT *,at.pic FROM activity_transaction at ";
        $sql.=" LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.=" WHERE at.customer_id=".$id."";
		$sql.=" ORDER BY at.activity_date DESC ";
		$query=$this->db->query($sql);
		return $query->result_array();
    }
	
    function getLastJob($id){
        $sql ="SELECT *,j.id,j.pic,((p.invest + (p.invest * j.ppn / 100) - j.discount) ) as total FROM jobs j ";
		$sql.="LEFT JOIN products p ON p.id=j.product_id ";
		$sql.="LEFT JOIN customers c ON c.id=j.customer_id ";
        $sql.=" WHERE j.customer_id=".$id."";
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getSearch(){
        if($this->input->post('search')):
            if($this->input->post('pic_search')<> "-"):
                $this->db->where('rev',$this->input->post('pic_search'));
            endif;
            
            if($this->input->post('group_search')<> "-"):
                $this->db->where('group_id',$this->input->post('group_search'));
            endif;
            
            if($this->input->post('sector_search')<> "-"):
                $this->db->where('sector_id',$this->input->post('sector_search'));
            endif;
            
            if($this->input->post('source_search')<> "-"):
                $this->db->where('source_id',$this->input->post('source_search'));
            endif;
            
            if($this->input->post('status_search')<>"-"):
                $this->db->where('status_id',$this->input->post('status_search'));
            endif;
            
        else:
            $this->db->where('customer_name','error');
        endif;
        $query = $this->db->get('customers');
		return $query->result_array();
	}
    
    function getUsers(){
		$sql ="SELECT *,u.id FROM users u ";
		$sql.="LEFT JOIN user_group g ON g.id=u.group_id ";
		$sql.="LEFT JOIN user_position p ON p.id=u.pos_id ";
        $sql.=" WHERE pos_id=".$this->config->item('ta');
        $sql.=" AND sales=1 ";
        $sql.=" AND u.enabled=1 ";
        $sql.=" AND group_id=".$this->config->item('user');
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getCek(){
        if(($this->session->userdata('user_group')== $this->config->item('admin')) || ($this->session->userdata('user_group')== $this->config->item('co_admin'))):
			$rev =  $this->input->post('rev');
		elseif($this->session->userdata('user_pos')== $this->config->item('se')):
            $rev =  $this->session->userdata('user_ta');
        elseif($this->session->userdata('user_pos')== $this->config->item('ta')):
			$rev =  $this->session->userdata('user_id');
        else:
            $rev =  $this->session->userdata('user_id');    
		endif;
            
        $sql =" SELECT * FROM customers u ";
		$sql.=" WHERE customer_name = '".$this->input->post('name')."'";
        $sql.=" LIMIT 1";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getData($id){
		$sql ="SELECT *,c.id  FROM customers c ";
		$sql.="LEFT JOIN customer_group cg ON cg.id =c.group_id ";
		$sql.="LEFT JOIN customer_sector cs ON cs.id=c.sector_id ";
        $sql.="LEFT JOIN customer_source csr ON csr.id=c.source_id ";
        $sql.="LEFT JOIN customer_status cst ON cst.id=c.status_id ";
        $sql.="WHERE c.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    function getDataProspect($id){
		$sql ="SELECT *,p.id  FROM prospects p ";
        $sql.="WHERE p.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    function getSave(){
		if(($this->session->userdata('user_group')== $this->config->item('admin')) || ($this->session->userdata('user_group')== $this->config->item('co_admin'))):
			$rev  =  $this->input->post('ta');
			$rev2 =  $this->input->post('se');;
			$rev3 =  $this->input->post('ae');;
			
			if($this->input->post('se_approve')):
				$se	  	=  1;
				$se_name=  $this->input->post('se_approve');
			else:
				$se	    =  0;
				$se_name= '';
			endif;
			
			if($this->input->post('spv_approve')):
				$spv	  	=  1;
				$spv_name=  $this->input->post('spv_approve');
			else:
				$spv	    =  0;
				$spv_name= '';
			endif;
			
		elseif($this->session->userdata('user_pos')== $this->config->item('ta')):
            $rev =  $this->session->userdata('user_id');
			$rev2 =  '';
			$rev3 =  '';
			$se	  =  0;
			
			$this->db->where('pos_id',$this->config->item('se'));
			if($this->session->userdata('user_group')==$this->config->item('user_dna')):
				$this->db->where('group_id',$this->config->item('user_dna'));
			else:
				$this->db->where('group_id',$this->config->item('user'));
			endif;	
			$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.se_name=users.username AND se_approve=0 ) ASC');
			$Q=$this->db->get('users',1);
			$user=$Q->row_array();
			$se_name=$user['username'];
			
			//$se_name='';
			$spv   =  0;
			$spv_name='';
        elseif($this->session->userdata('user_pos')== $this->config->item('se')):
			$rev  =  '';
			$rev2 =  $this->session->userdata('user_id');
			$rev3 =  '';
			$se	  =  1 ;
			$se_name=$this->session->userdata('user_id');
			$spv   =  0;
			$spv_name='';
        elseif($this->session->userdata('user_pos')== $this->config->item('ae')):
			$rev  =  '';
			$rev2 =  '';
			$rev3 =  $this->session->userdata('user_id');
			$se	  =  0;
			
			$this->db->where('pos_id',$this->config->item('se'));
			if($this->session->userdata('user_group')==$this->config->item('user_dna')):
				$this->db->where('group_id',$this->config->item('user_dna'));
			else:
				$this->db->where('group_id',$this->config->item('user'));
			endif;	
			$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.se_name=users.username AND se_approve=0 ) ASC');
			$Q=$this->db->get('users',1);
			$user=$Q->row_array();
			$se_name=$user['username'];
			//$se_name='';
			$spv   =  0;
			$spv_name='';
		else:
            $rev =  $this->session->userdata('user_id'); 
			$rev2 =  '';
			$rev3 =  '';
			$rev =  $this->session->userdata('user_id');
			$rev2 =  '';
			$rev3 =  '';
			$se	  =  0;
			$this->db->where('pos_id',$this->config->item('se'));
			if($this->session->userdata('user_group')==$this->config->item('user_dna')):
				$this->db->where('group_id',$this->config->item('user_dna'));
			else:
				$this->db->where('group_id',$this->config->item('user'));
			endif;	
			$this->db->order_by('(SELECT COUNT(*) FROM customers WHERE customers.se_name=users.username AND se_approve=0 ) ASC');
			$Q=$this->db->get('users',1);
			$user=$Q->row_array();
			$se_name=$user['username'];
			//$se_name='';
			$spv   =  0;
			$spv_name='';	
		endif;	
		
		if($this->session->userdata('user_group')== $this->config->item('user')):
			$division = 'IN HOUSE';
		elseif($this->session->userdata('user_group')== $this->config->item('user_dna')):
			$division = 'DNA';
		else:
			$division = 'IN HOUSE';
		endif;
		
		
			
		
		
        $value = array  (
                            'sector_id'     =>  trim($this->input->post('sector')),
							'division'		=>	$division,
                            'rev_group'     =>  0,
                            'group_id'      =>  trim($this->input->post('group')),
                            'source_id'     =>  trim($this->input->post('source')),
                            'status_id'     =>  trim($this->input->post('status')),
                            'customer_name' =>  $this->input->post('name'),
                            'customer_from' =>  $this->input->post('from'),
                            'pic'           =>  $this->input->post('pic'),
							'fn_pic'        =>  $this->input->post('fn'),
                            'pic_pos'       =>  $this->input->post('pic_pos'),
							'fn_pic2'       =>  $this->input->post('fn2'),
                            'pic2'          =>  $this->input->post('pic'),
                            'pic2_pos'      =>  $this->input->post('pic_pos'),
                            'fax'           =>  $this->input->post('fax'),
                            'address'       =>  $this->input->post('address'),
                            'tlp'           =>  $this->input->post('tlp'),
                            'ext'           =>  $this->input->post('ext'),
							'ext'           =>  $this->input->post('ext2'),
                            'hp'           	=>  $this->input->post('hp'),
							'hp2'          	=>  $this->input->post('hp2'),
                            'email'         =>  $this->input->post('email'),
							'email2'         =>  $this->input->post('email2'),
                            'city'          =>  $this->input->post('city'),
                            'zip_code'      =>  $this->input->post('zipcode'),
                            'desc'          =>  $this->input->post('desc'),
                            'rev'           =>  $rev,
                            'rev2'          =>  $rev2,
                            'rev3'          =>  $rev3,
							'se_approve'	=>	$se,
							'se_name'		=>	$se_name,
							'spv_approve'	=>	$spv,
							'spv_name'		=>	$spv_name,
                            'enabled'       =>  1,
                            'user_stamp'    =>  $this->session->userdata('user_id'),
                            'date_stamp'    =>  date('Y-m-d')
                        );
        $this->db->insert('customers',$value);                
    }
    
    function getUpdate(){	
		if(($this->session->userdata('user_group')== $this->config->item('admin')) || ($this->session->userdata('user_group')== $this->config->item('co_admin'))):
			$rev  =  $this->input->post('ta');
			$rev2 =  $this->input->post('se');
			$rev3 =  $this->input->post('ae');
			
			if($this->input->post('se_approve')):
				$se	  	=  1;
				$se_name=  $this->input->post('se_approve');
			else:
				$se	    =  0;
				$se_name= '';
			endif;
			
			if($this->input->post('spv_approve')):
				$spv	  	=  1;
				$spv_name=  $this->input->post('spv_approve');
			else:
				$spv	    =  0;
				$spv_name= '';
			endif;
			
		elseif($this->session->userdata('user_pos')== $this->config->item('ta')):
            $rev =  $this->session->userdata('user_id');
			$rev2 =  '';
			$rev3 =  '';
			$se	  =  1;
			$se_name='';
			$spv   =  0;
			$spv_name='';
        elseif($this->session->userdata('user_pos')== $this->config->item('se')):
			$rev  =  '';
			$rev2 =  $this->session->userdata('user_id');
			$rev3 =  '';
			$se	  =  1 ;
			$se_name=$this->session->userdata('user_id');
			$spv   =  0;
			$spv_name='';
        elseif($this->session->userdata('user_pos')== $this->config->item('ae')):
			$rev  =  '';
			$rev2 =  '';
			$rev3 =  $this->session->userdata('user_id');
			$se	  =  0;
			$se_name='';
			$spv   =  0;
			$spv_name='';
		elseif($this->session->userdata('user_pos')== $this->config->item('spv')):
			$rev  =  '';
			$rev2 =  '';
			$rev3 =  $this->session->userdata('user_id');
			$se	  =  1;
			$se_name='Old Data';
			$spv   =  0;
			$spv_name='';
		else:
            $rev =  $this->session->userdata('user_id'); 
			$rev2 =  '';
			$rev3 =  '';
			$rev =  $this->session->userdata('user_id');
			$rev2 =  '';
			$rev3 =  '';
			$se	  =  0;
			$se_name='';
			$spv   =  0;
			$spv_name='';	
		endif;
		
		
		
		if($this->session->userdata('user_group')== $this->config->item('admin')):	
			$value = array  (
                            'sector_id'     =>  trim($this->input->post('sector')),
                            'rev_group'     =>  0,
                            'group_id'      =>  trim($this->input->post('group')),
                            'source_id'     =>  trim($this->input->post('source')),
                            'status_id'     =>  trim($this->input->post('status')),
                            'customer_name' =>  $this->input->post('name'),
                            'customer_from' =>  $this->input->post('from'),
							'fn_pic'        =>  $this->input->post('fn'),
                            'pic'           =>  $this->input->post('pic'),
                            'pic_pos'       =>  $this->input->post('pic_pos'),
							'fn_pic2'       =>  $this->input->post('fn2'),
                            'pic2'          =>  $this->input->post('pic'),
                            'pic2_pos'      =>  $this->input->post('pic_pos'),
                            'fax'           =>  $this->input->post('fax'),
                            'address'       =>  $this->input->post('address'),
                            'tlp'           =>  $this->input->post('tlp'),
                            'ext'           =>  $this->input->post('ext'),
							'ext2'          =>  $this->input->post('ext2'),
                            'hp'            =>  $this->input->post('hp'),
							'hp2'          	=>  $this->input->post('hp2'),
                            'email'         =>  $this->input->post('email'),
							'email2'        =>  $this->input->post('email2'),
                            'city'          =>  $this->input->post('city'),
                            'zip_code'      =>  $this->input->post('zipcode'),
                            'desc'          =>  $this->input->post('desc'),
                            'rev'           =>  $rev,
                            'rev2'          =>  $rev2,
                            'rev3'          =>  $rev3,
							'se_approve'	=>	$se,
							'se_name'		=>	$se_name,
							'spv_approve'	=>	$spv,
							'spv_name'		=>	$spv_name,
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );
		else:
			$value = array  (
                            'sector_id'     =>  trim($this->input->post('sector')),
                            'rev_group'     =>  0,
                            'group_id'      =>  trim($this->input->post('group')),
                            'source_id'     =>  trim($this->input->post('source')),
                            'status_id'     =>  trim($this->input->post('status')),
                            'customer_name' =>  $this->input->post('name'),
                            'customer_from' =>  $this->input->post('from'),
                            'pic'           =>  $this->input->post('pic'),
                            'pic_pos'       =>  $this->input->post('pic_pos'),
                            'pic2'          =>  $this->input->post('pic'),
                            'pic2_pos'      =>  $this->input->post('pic_pos'),
                            'fax'           =>  $this->input->post('fax'),
                            'address'       =>  $this->input->post('address'),
                            'tlp'           =>  $this->input->post('tlp'),
                            'ext'           =>  $this->input->post('ext'),
							'ext2'          =>  $this->input->post('ext2'),
                            'hp'            =>  $this->input->post('hp'),
                            'email'         =>  $this->input->post('email'),
							'email2'        =>  $this->input->post('email2'),
                            'city'          =>  $this->input->post('city'),
                            'zip_code'      =>  $this->input->post('zipcode'),
                            'desc'          =>  $this->input->post('desc'),
							'se_approve'	=>	$se,
							'se_name'		=>	$se_name,
							'spv_approve'	=>	$spv,
							'spv_name'		=>	$spv_name,
                            //'rev'           =>  $rev,
                            //'rev2'          =>  $rev2,
                            //'rev3'          =>  $rev3,
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );	
		endif;	
        $this->db->where('id',$this->input->post('id'));                 
        $this->db->update('customers',$value);                
    }
    
    function getRemove($id){
        $this->db->where('id',$id);
        $this->db->delete('customers');
    }
    
    function getRemoveProspect(){
        $this->db->where('rev','');
        $this->db->delete('prospects');
    }
	
	function getRemoveProspectUser(){
		$value['rev']='';
        $this->db->where_not_in('rev','');
        $this->db->update('prospects',$value);
    }
	
	function getProvideProspect($user=''){
		$sql ="SELECT *,u.id FROM users u ";
        $sql.=" WHERE pos_id=".$this->config->item('ta');
        $sql.=" AND sales=1 ";
		if($user){
		$sql.=" AND u.username='".$user."'";
		}
        $sql.=" AND group_id=".$this->config->item('user');
		$query=$this->db->query($sql);
		$rows=$query->result_array();
		foreach($rows as $user):
			$sql ="SELECT *,p.id  FROM prospects p ";
			$sql.="WHERE p.rev='' ORDER BY RAND() LIMIT 100";
			$query=$this->db->query($sql);
			$rows=$query->result_array();
			foreach($rows as $cust):
				$value['rev'] = $user['username'];
				$this->db->where('id',$cust['id']);
				$this->db->update('prospects',$value);
			endforeach;
		endforeach;
	}
	
	function getProvideProspectCustomer($id){
		$sql ="SELECT * FROM prospects u ";
		$sql.="WHERE id=".$id." LIMIT 1";
		$query=$this->db->query($sql);
		$row=$query->row_array();
		$this->db->orderby('id','DESC');
		$query=$this->db->get('customers');
		$ids = $query->row_array();
		if(COUNT($ids)>=1):
			$ids = $ids['id'] + 1;
		else:
			$ids = 0 + 1;
		endif;
		
		$value = array  (
							'id'			=>	trim($ids),
                            'sector_id'     =>  1,
                            'group_id'      =>  1,
                            'source_id'     =>  1,
                            'status_id'     =>  1,
                            'customer_name' =>  $row['customer_name'],
                            'pic'           =>  $row['pic'],
                            'pic_pos'       =>  $row['pic_pos'],
                            'pic2'          =>  $row['pic2'],
                            'pic2_pos'      =>  $row['pic2_pos'],
                            'fax'           =>  $row['fax'],
                            'address'       =>  $row['address'],
                            'tlp'           =>  $row['tlp'],
                            'email'         =>  $row['email'],
                            'city'          =>  $row['city'],
                            'zip_code'      =>  $row['zip_code'],
                            'desc'          =>  $row['desc'],
                            'rev'           =>  $row['rev'],
                            'enabled'       =>   1,
                            'user_stamp'	=>  $this->session->userdata('user_id'),
                            'date_stamp'	=>  date('Y-m-d')
                        );
        $this->db->insert('customers',$value);
		
		$this->db->where('id',$id);
		$this->db->delete('prospects');
	}
}